var pool = require("../conf/db").pool;
var logger = require('../conf/log4').logger('dao');

function  saveRank(data) {
    pool.getConnection(function(err, connection) {
        var sql = "INSERT into t_jd_pro_ranking(fetch_time,page_type,category,is_jd_deliver,keyword,rank,sku_id,pro_name,is_jd_zy,";
            sql += "page_num,page_set,pro_price,comment_total_number,is_extension,extension_type,img_url,pro_url)";
            sql += "values(CURDATE(),0,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
        var rank = (data.pageNum-1)*60 + data.index;
        var type = data.isAd+ data.isActivity;
        connection.query(sql,
            [data.psort,data.wtype,data.keyword,rank, data.sku,data.name, data.isZy,data.pageNum,data.index,
                data.price||0,data.commit,data.isAd,type,data.img,data.url],
            function (err, rows) {
                if(err) console.log(err);
                connection.release();
            });
    });
};

function  saveMbRank(data) {
    pool.getConnection(function(err, connection) {
        var sql = "INSERT into t_jd_pro_ranking(fetch_time,page_type,category,is_jd_deliver,keyword,rank,sku_id,pro_name,is_jd_zy,";
        sql += "page_num,page_set,pro_price,comment_total_number,is_extension,img_url,good_rate,promotion_flag,diffmobileprice)";
        sql += "values(CURDATE(),1,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
        var rank = (data.pageNum-1)*11 + data.index;
        var type = data.targetUrl==''?0:1;
        connection.query(sql,
            [data.psort,data.wtype,data.keyword,rank, data.wareId,data.wname, data.self,data.pageNum,data.index,
                data.jdPrice,data.totalCount||0,type,data.imageurl,data.good,data.promotionFlag.toString(),data.diffMobilePrice],
            function (err, rows) {
                if(err) console.log(err);
                //console.log(rows)
                connection.release();
            });
    });
};


function  saveHotKeyRank(data) {
    pool.getConnection(function(err, connection) {
        var sql = "INSERT into t_jd_hot_key_ranking(fetch_time,keyword,rank,sku_id,pro_name,is_jd_zy,";
        sql += "page_num,page_set,pro_price,comment_total_number,is_extension,img_url,pro_url)";
        sql += "values(CURDATE(),?,?,?,?,?,?,?,?,?,?,?,?);";
        connection.query(sql,
            [data.keyword,data.rank, data.sku,data.name, data.isZy,data.pageNum,data.index,
                data.price||0,data.commit,data.isAd,data.img,data.url],
            function (err, rows) {
                if(err) console.log(err);
                connection.release();
            });
    });
};


function  saveComment(data) {
    var imgs = data.images;
    var img_url = "";
    for(var i in imgs){
        img_url += imgs[i].imgUrl+";";
    }
    pool.getConnection(function(err, connection) {
        var sql = "replace into t_jd_hot_key_comment(fetch_time,id,sku_id,comment_page_num,comment_page_set,";
        sql += "creation_time,is_top,pro_color,pro_size,pro_name,reference_time,score,";
        sql += "user_province,nick_name,user_client,isMobile,days,content,vip_lv,vip_lv_name,img_url)";
        sql += "values(CURDATE(),?,?,?,?, ?,?,?,?,?,?,?, ?,?,?,?,?, ?,?,?,? );";
        connection.query(sql,
            [data.id,data.referenceId, data.pageNum,data.index,data.creationTime,data.isTop,data.productColor,data.productSize,
                data.referenceName,data.referenceTime,data.score, data.userProvince,data.nickname,data.userClientShow,
                data.isMobile,data.days,data.content,data.userLevelId,data.userLevelName,img_url],
            function (err, rows) {
                if(err) console.log(err);
                connection.release();
            });
    });
};

function updateCount(){
	pool.getConnection(function(err,conn){
	var sql = "replace into t_jd_comment_count(creation_time,sku_id,ct) ";
		sql += "select date(b.creation_time),b.sku_id,count(1) from t_jd_hot_key_ranking a inner join t_jd_hot_key_comment b using(sku_id) where a.fetch_time=CURDATE() ";
		sql+="GROUP BY date(b.creation_time),b.sku_id";
	conn.query(sql,function(err,rows){
		if(err)console.log(err);
		conn.release();
		
	});
	});
	
};


function findKeyword(foo) {
    // var sql = "select keyword from t_jd_keyword_type_relative where keyword='iphone7钢化膜' limit 1";
    var sql = "select category,keyword,is_jd_deliver From t_base_url where source='jd' and is_used=1 and (type='type_rank' ) order by keyword ";
    //console.log(sql);
    pool.getConnection(function(err, connection) {
        connection.query(sql,function (err, rows, fields) {
            if (err) {console.log('[query] - :'+err);return;}
            foo(rows);
        });
    });
};
function findHotKey(time,foo,jobep) {  
    var sql = "select `key` from t_esr_search_key where source='jd' and fetchTime='"+time+"' and qre>60 and team=1 and isUse=1 ";
    //console.log(sql);
    pool.getConnection(function(err, connection) {
        connection.query(sql,function (err, rows, fields) {
            if (err) {console.log('[query] - :'+err);return;}
            foo(rows,jobep);
        });
    });
};
function findHotKeySku(time,foo,ep) {
    // var sql = "select keyword from t_jd_keyword_type_relative where keyword='iphone7钢化膜' limit 1";
    var sql = "select DISTINCT sku_id from t_jd_hot_key_ranking where fetch_time='"+time+"'";
    //console.log(sql);
    pool.getConnection(function(err, connection) {
        connection.query(sql,function (err, rows, fields) {
            if (err) {console.log('[query] - :'+err);return;}
            foo(rows,ep);
        });
    });
};

function findMbKeyword(foo) {
    // var sql = "select keyword from t_jd_keyword_type_relative where keyword='iphone7钢化膜' limit 1";
    var sql = "select category,keyword,is_jd_deliver From t_base_url where source='jd' and is_used=1 and (type='type_rank_mb' ) order by keyword ";
    //var sql = "select DISTINCT sku_id from t_jd_hot_key_comment ";
    //console.log(sql);
    pool.getConnection(function(err, connection) {
        connection.query(sql,function (err, rows, fields) {
            if (err) {console.log('[query] - :'+err);return;}
            foo(rows);
        });
    });
};


/**
 * 执行事务，批量提交
 * @param sql       执行的SQL语句
 * @param params    参数数组
 * @param callback  回调方法
 */
function execTrans(sql,params, callback) {
    pool.getConnection(function (e, connection) {
        if (e) {
            if(callback)callback(e, null);
        }else{
            connection.beginTransaction(function (err) {
                if (err) {
                    if(callback)callback(err, null);
                }
                logger.info("开始执行transaction，共执行" + params.length + "条数据");
                var EventProxy = require('eventproxy');
                var job = new EventProxy();


                params.forEach(function (data) {
                    connection.query(sql, data, function (tErr) {
                        if (tErr) {
                            logger.error(data);
                            logger.error("执行失败，ERROR：" + tErr);
                            job.emit("query_err",tErr);
                        }else{
                            job.emit("query_done");
                        }
                    });

                });

                job.after("query_done",params.length,function () {
                    connection.commit(function (err, info) {
                        if (err) {
                            logger.error("事务提交失败，ERROR：" + err);
                            connection.rollback(function () {
                                connection.release();
                                if(callback) callback(err, null);
                            });
                        } else {
                            connection.release();
                            if(callback)callback(null, info);
                        }

                    });
                });

                job.once("query_err",function (err) {
                    connection.rollback(function () {
                        connection.release();
                        if(callback)callback(err, null);
                    });
                })
            });
        }
    });
}



module.exports.saveRank = saveRank;
module.exports.saveComment = saveComment;
module.exports.saveHotKeyRank = saveHotKeyRank;
module.exports.saveMbRank = saveMbRank;
module.exports.findKeyword = findKeyword;
module.exports.findHotKey = findHotKey;
module.exports.findMbKeyword = findMbKeyword;
module.exports.findHotKeySku = findHotKeySku;
module.exports.updateCount = updateCount;

module.exports.execTrans = execTrans;